Re: [SQL] copying rows

At 1:38 +0300 on 26/8/98, Walt Bigelow wrote:


> I have a table that stores spot info for our tape label system.  I want to
> be able to copy all records with a specific librarynumber back into the
> same table, but with a different librarynumber.
>
> Something similar to this (which of course does not work)
>
> select into tblspotinfo (librarynumber) values ('6666') select spotnumber,
> spottitle, isci, trt, date, timecode from tblspotinfo where librarynumber
> = '9988';
>
> Of course it does not like me overloading the select into command like
> that.  Is there a simple way to do this that I am just missing?  I just
> want to duplicate the data related to librarynumber 9988 and make the new
> librarynumber 6666.

It's just a matter of syntax. What you want to do is add new rows into a
table. That implies an INSERT rather than a select (select into creates a
new table, not inserts rows into an existing one).

So, we check the syntax for INSERT to see if there is any help there.

testing=> \h insert
Command: insert
Description: insert tuples
Syntax:
insert into <class_name> [(<attr1>...<attrN>)]
        [values (<expr1>...<exprN>); |
        select <expr1>,...<exprN> [from <from_clause>] [where <qual>];

Indeed there is! Your syntax should be:

INSERT INTO tblspotinfo (
    librarynumber,
    spotnumber,
    spottitle,
    isci,
    trt,
    date,
    timecode )
SELECT
    6666,
    spotnumber,
    spottitle,
    isci,
    trt,
    date,
    timecode
FROM tblsoptinfo
WHERE librarynumber = 9988;

Voila!

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma